{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开发生额及余额表\n",
    "\n",
    "wb=xw.Book(r'D:\\BaiduSyncdisk\\Ajupy\\技术凭证整理\\发生额及余额表.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取科目\n",
    "\n",
    "df1=wb.sheets[0].range('a7').current_region.options(pd.DataFrame,index=False).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保留科目类别、科目编码、科目名称三列，第一行是空行，所以从第一行取\n",
    "df2=df1.loc[1:,['科目类别','科目编码','科目名称']].fillna(0)\n",
    "df2['科目编码']=df2['科目编码'].astype('str').str.strip()\n",
    "df2['科目名称']=df2['科目名称'].astype('str').str.strip()\n",
    "df2=df2[df2['科目类别']!=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>科目类别</th>\n",
       "      <th>科目编码</th>\n",
       "      <th>科目名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>资产</td>\n",
       "      <td>1001</td>\n",
       "      <td>库存现金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>中信基本户-1715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>100202</td>\n",
       "      <td>浦发银行-3896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>资产</td>\n",
       "      <td>100203</td>\n",
       "      <td>招商银行-0106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>损益</td>\n",
       "      <td>660306</td>\n",
       "      <td>利息收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>损益</td>\n",
       "      <td>6711</td>\n",
       "      <td>营业外支出</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>损益</td>\n",
       "      <td>671103</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>损益</td>\n",
       "      <td>6901</td>\n",
       "      <td>以前年度损益调整</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284</th>\n",
       "      <td>合计：</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>279 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    科目类别    科目编码        科目名称\n",
       "1     资产    1001        库存现金\n",
       "2     资产    1002        银行存款\n",
       "3     资产  100201  中信基本户-1715\n",
       "4     资产  100202   浦发银行-3896\n",
       "5     资产  100203   招商银行-0106\n",
       "..   ...     ...         ...\n",
       "279   损益  660306        利息收入\n",
       "280   损益    6711       营业外支出\n",
       "281   损益  671103          其他\n",
       "282   损益    6901    以前年度损益调整\n",
       "284  合计：       0           0\n",
       "\n",
       "[279 rows x 3 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 生成长科目"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 取出科目(x):\n",
    "    str1=''\n",
    "    str2=''\n",
    "    str3=''\n",
    "    str4=''\n",
    "    if len(x)>=4 :\n",
    "        try:\n",
    "            str1=df2[df2['科目编码']==x[0:4]]['科目名称'].iloc[0]\n",
    "        except:\n",
    "            str1=''\n",
    "    if len(x) >=6 :\n",
    "        try:\n",
    "            str2=df2[df2['科目编码']==x[0:6]]['科目名称'].iloc[0]\n",
    "        except:\n",
    "            str2=''\n",
    "    if len(x) >=8 :\n",
    "        try:\n",
    "            str3=df2[df2['科目编码']==x[0:8]]['科目名称'].iloc[0]\n",
    "        except:\n",
    "            str3=''\n",
    "\n",
    "    if len(x)>=10:\n",
    "        try:\n",
    "            str4=df2[df2['科目编码']==x[0:10]]['科目名称'].iloc[0]\n",
    "        except:\n",
    "            str4=''\n",
    "    a=[str1,str2,str3,str4]\n",
    "    result=\"-\".join([x for x in a if x!=''])\n",
    "    return result\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['长科目']=df2['科目编码'].apply(取出科目)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 长科目写入到wb\n",
    "wb.sheets[1].range('a1').value=df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "有了完整的科目表后，就可以看着科目表来筛选凭证了。~，先链接到凭证吧"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=pd.read_excel(r'技术凭证管理1-2月.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\admin\\AppData\\Local\\Temp\\ipykernel_23360\\1304698171.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df3['科目名称'].fillna('',inplace=True)\n"
     ]
    }
   ],
   "source": [
    "df3['摘要']=df3['摘要'].astype('str')\n",
    "df3=df3[~df3['摘要'].str.contains('结转期间损益')]\n",
    "df3['科目名称'].fillna('',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=df3.drop(['凭证总金额','审核人'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 科目名称筛选\n",
    "wb.selection.value=df3.query(\"科目名称.str.contains('预收账款-技术服务收入-家政次卡收入')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 摘要筛选\n",
    "wb.selection.value=df3.query(\"摘要.str.contains('在线收款')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb.selection.value=df3[(df3['凭证字号']=='记-0711') | (df3['制单日期']=='2024/2/29')]\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
